﻿using System;
using System.Collections.Generic;
using System.Text;
using DotNet.Model;
using DotNet.Common;
using DotNet.Data.Factory;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace DotNet.Data
{
    public class ContactDal
    {
        IDBHelp db = DBHelp.Create();
        private static Hashtable htSyn = Hashtable.Synchronized(new Hashtable());
        StringBuilder strSql = new StringBuilder();
        #region 是否存在该记录
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int contactId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select count(1) from Contact where contactId=@contactId ");
            htSyn.Add("contactId", contactId);
            return db.IsExists(strSql.ToString(), htSyn);
        }
        #endregion

        #region 获得相关联系列表
        /// <summary>
        /// 获得相关联系列表
        /// </summary>
        /// <returns></returns>
        public IList<Contact> GetModel()
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);

            strSql.Append("select contactId,Name,Post,Sex,Email,contactDate,Mark,qq,Tel from Contact ");
            return getProductsBySql(strSql.ToString());
        }
        #endregion

        #region 根据相关联系号查询实体
        /// <summary>
        /// 根据相关联系号查询实体
        /// </summary>
        /// <param name="contactId"></param>
        /// <returns></returns>
        public Contact GetModel(int contactId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select contactId,Name,Post,Sex,Email,contactDate,Mark,qq,Tel from Contact ");
            strSql.Append(" where contactId=@contactId ");
            htSyn.Add("@contactId", contactId);
            DataTable dt = db.GetDataTable(strSql.ToString(), htSyn);
            Contact contact = new Contact();
            if (dt.Rows.Count>0)
            {
                contact.contactId = Convert.ToInt32(dt.Rows[0]["contactId"]);
                contact.Name = dt.Rows[0]["Name"].ToString(); ;
                contact.Post = dt.Rows[0]["Post"].ToString();
                contact.Sex = Convert.ToBoolean(dt.Rows[0]["Sex"].ToString());
                contact.Email = dt.Rows[0]["Email"].ToString();
                contact.contactDate = Convert.ToDateTime(dt.Rows[0]["contactDate"]);
                contact.Mark = dt.Rows[0]["Mark"].ToString();
                contact.qq = dt.Rows[0]["qq"].ToString();
                contact.Tel = dt.Rows[0]["Tel"].ToString();
            }
            return contact;
        }
        #endregion

        #region 修改相关联系信息
        /// <summary>
        /// 修改相关联系信息
        /// </summary>
        /// <param name="contact"></param>
        /// <returns></returns>
        public bool Update(Contact contact)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("update Contact set ");
            strSql.Append("Name=@Name,");
            strSql.Append("Post=@Post,");
            strSql.Append("Sex=@Sex,");
            strSql.Append("Email=@Email,");
            strSql.Append("contactDate=@contactDate,");
            strSql.Append("Mark=@Mark,");
            strSql.Append("qq=@qq,");
            strSql.Append("tel=@tel");
            strSql.Append(" where contactId=@contactId ");
            htSyn.Add("@contactId",contact.contactId);
            htSyn.Add("@Name", contact.Name);
            htSyn.Add("@Post", contact.Post);
            htSyn.Add("@Sex", contact.Sex);
            htSyn.Add("@Email",contact.Email);
            htSyn.Add("@contactDate",contact.contactDate);
            htSyn.Add("@Mark",contact.Mark);
            htSyn.Add("@qq", contact.qq);
            htSyn.Add("@tel",contact.Tel);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 删除相关联系根据相关联系号
        /// <summary>
        /// 删除相关联系根据相关联系号
        /// </summary>
        /// <param name="contactId"></param>
        /// <returns></returns>
        public bool Delete(int contactId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("delete from Contact where contactId=@contactId");
            htSyn.Add("contactId", contactId);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 添加相关联系
        /// <summary>
        /// 添加相关联系
        /// </summary>
        /// <param name="Contact"></param>
        /// <returns></returns>
        public bool Insert(Contact contact)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("insert into Contact([Name],[Post],[Sex],[Email],[contactDate],[Mark],[qq],[Tel]) values (@Name,@Post,@Sex,@Email,@contactDate,@Mark,@qq,@Tel)");
            htSyn.Add("@Name", contact.Name);
            htSyn.Add("@Post", contact.Post);
            htSyn.Add("@Sex", contact.Sex);
            htSyn.Add("@Email", contact.Email);
            htSyn.Add("@contactDate", contact.contactDate);
            htSyn.Add("@Mark", contact.Mark);
            htSyn.Add("@qq", contact.qq);
            htSyn.Add("@tel", contact.Tel);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 根据SQL语句获取集合
        /// <summary>
        ///根据SQL语句获取集合
        /// </summary>
        public IList<Contact> getProductsBySql(string strSql)
        {
            IList<Contact> list = new List<Contact>();
            DataTable dt = db.GetDataTable(strSql);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    Contact contact = new Contact();
                    contact.contactId = Convert.ToInt32(dr["contactId"]);
                    contact.Name = dr["Name"].ToString(); ;
                    contact.Post = dr["Post"].ToString();
                    contact.Sex =Convert.ToBoolean(dr["Sex"].ToString());
                    contact.Email = dr["Email"].ToString();
                    contact.contactDate = Convert.ToDateTime(dr["contactDate"].ToString());
                    contact.Mark = dr["Mark"].ToString();
                    contact.qq = dr["qq"].ToString();
                    contact.Tel = dr["tel"].ToString();
                    list.Add(contact);
                }
            }
            return list;
        }
        #endregion
    }
}
